package org.springboot.user;

import java.awt.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

 
@Service  
public class UserServiceImpl implements UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    
    public void deleteByCode(String code) { 
    	//较简单的情况，使用jdbcTemplate
        jdbcTemplate.update("delete from user_info where code = ?", code);
    }
   
    @Override
    public void add(String name, String code,String remark,String state,String create_time,String account_class) {  
    	//初始化namedParameterJdbcTemplate
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate); 
        //建立模型
        User model = new User();  
        model.setName(name);
        model.setCode(code);
        model.setRemark(remark);
        model.setState(state);
        model.setCreate_time(create_time);
        model.setAccount_class(account_class);
        //Sql语句
        String insertSql = "insert into user_info(name,code,remark,state,create_time,account_class) "
        		+ "values(:name,:code,:remark,:state,:create_time,:account_class)";
        //添加模型，并执行SQL语句
        SqlParameterSource paramSource = new BeanPropertySqlParameterSource(model);  
        namedParameterJdbcTemplate.update(insertSql, paramSource);  
    }


	@Override
	public void alterByCode(String code, String name, String remark) {

	  NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate); 
      User model = new User();  
      model.setCode(code);
      model.setName(name);
      model.setRemark(remark);
	  String updateSql="update user_info set name=:name,remark=:remark where code=:code";
	  
	  SqlParameterSource paramSource = new BeanPropertySqlParameterSource(model);  
      namedParameterJdbcTemplate.update(updateSql, paramSource);  
      
	}

	@Override
	public java.util.List<Map<String, Object>> queryBycode(String code) {
		// TODO Auto-generated method stub

	  NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate); 
	  User model = new User();  
	  model.setCode(code);
	  model.setState("unlocked");
	  String querySql="select * from user_info where code like'%'  :code  '%' and state=:state";
		  
	  SqlParameterSource paramSource = new BeanPropertySqlParameterSource(model);  
	  java.util.List<Map<String, Object>> list;
	  list =namedParameterJdbcTemplate.queryForList(querySql, paramSource);
	  Map map=list.get(0);
	  System.out.print(map.get("account_class"));
      return list;
	 
	   
	      		
	}

	@Override
	public java.util.List<Map<String, Object>> queryByname(String name) {
		// TODO Auto-generated method stub
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate); 
		  User model = new User();  
		  model.setName(name);
		  model.setState("unlocked");
		  String querySql="select * from user_info where name like'%'  :name  '%'";
			  
		  SqlParameterSource paramSource = new BeanPropertySqlParameterSource(model);  
		  java.util.List<Map<String, Object>> list;
		  list =namedParameterJdbcTemplate.queryForList(querySql, paramSource);
		     
	      return list;
	}

	@Override
	public java.util.List<Map<String, Object>> queryBydate(String[] date) {
		// TODO Auto-generated method stub
		 
		//delete from user_info where code = ?
		java.util.List<Map<String, Object>> list 
		= jdbcTemplate.queryForList("select * from user_info where create_time between ? and ? and state=?", date[0],date[1],"unlocked");
		return list;
	}

	@Override
	public java.util.List<Map<String, Object>> queryByClass(String Class) {
		// TODO Auto-generated method stub
		System.out.print(Class);
		java.util.List<Map<String, Object>> list 
		= jdbcTemplate.queryForList("select * from user_info where account_class=? and state=?", Class,"unlocked");
		return list;
	}

	@Override
	public void setState(String code, String state) {
		// TODO Auto-generated method stub
		  NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate); 
	      User model = new User();  
	      model.setCode(code);
	      model.setState(state);
		  String updateSql="update user_info set state=:state where code=:code";
		  
		  SqlParameterSource paramSource = new BeanPropertySqlParameterSource(model);  
	      namedParameterJdbcTemplate.update(updateSql, paramSource);  
	}  
}
